package common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;

import objects.Account;

public class SQLMgr
{
	static private ExecutorService Exec;
	static SQLMgr _self;
        public static byte CharacterNumb;
	
	public SQLMgr(byte n)
	{
		if(_self != null)return;//Only 1 Instance
		_self = this;
		Exec = Executors.newFixedThreadPool(n);//n threads
	}
	
	//Try to connect, and returns the Number of accounts
	public static synchronized long onInitTest()
	{
		if(_self==null)return -1;
		Future<Long> result = Exec.submit(new Callable<Long>()
		{
			public Long call() throws Exception
			{
				Connection DB = DriverManager.getConnection("jdbc:mysql://"+RealmConf._dbHost+"/"+RealmConf._dbName,RealmConf._dbUser,RealmConf._dbPass);
				Statement stat = DB.createStatement();
				ResultSet RS = stat.executeQuery("SELECT COUNT(*) FROM accounts;");
				RS.first();
				Long L = RS.getLong(1);
				RS.close();
				stat.close();
				DB.close();
				return L;
			}
		});
		
		//Try to get the result, else return -1
		try {
			return result.get();
		} catch (Exception e) { e.printStackTrace(); return -1; }
	}

	//Verify an account, & returns account GUID
	public static synchronized long verifAccount(final String account,final String password,final String hashKey,final String address)
	{
		if(_self==null)return -1;
		
		Future<Long> result = Exec.submit(new Callable<Long>()
				{
					public Long call() throws Exception
					{
						Connection DB = DriverManager.getConnection("jdbc:mysql://"+RealmConf._dbHost+"/"+RealmConf._dbName,RealmConf._dbUser,RealmConf._dbPass);
						Statement stat = DB.createStatement();
						ResultSet RS = stat.executeQuery(
								"SELECT guid,password FROM accounts "+
								"WHERE account = '"+account+"';");
						//If there is no account
						if(!RS.first())
						{
							RS.close();
							stat.close();
							DB.close();
							return (long)-4;
						}
						//Get Values
						long returnedValue = RS.getLong("guid");//Get Account GUID
						String pass = RS.getString("password");
						//Close the ResultSet
						RS.close();
						//Verification of the password: If wrong
						if(!CryptMgr.CryptPassword(hashKey, pass).equals(password))
						{
							RS.close();
							stat.close();
							DB.close();
							return (long)-4;
						}
						//Verification in BanList
						RS = stat.executeQuery(
								"SELECT unban_date FROM banlist "+
								"WHERE value = '"+account+"' OR value = '"+address+"';");
						boolean banned = false;
						//If there is results
						if(RS.first())
						{
							long unbanDate = RS.getLong("unban_date");
							//If the ban is ended, delete the row
							if(unbanDate < System.currentTimeMillis() && unbanDate != -1)
								stat.execute("DELETE FROM banlist WHERE value = '"+account+"' OR value = '"+address+"';");
							else
								banned = true;
						}
						//Close all resources
						RS.close();
						stat.close();
						DB.close();
						
						//Return GUID or Error ID
						if(banned) returnedValue = -3;
						return returnedValue;
					}
				});
				
		//Try to get the account guid, else return -2
		try {
			return result.get();
		} catch (Exception e) { e.printStackTrace(); return -2; }
	}

	public static synchronized  Account getAccountDataByID(final long guid)
	{
		if(_self==null)return null;
		
		Future<Account> account = Exec.submit(new Callable<Account>()
				{
					public Account call() throws Exception
					{
						Connection DB = DriverManager.getConnection("jdbc:mysql://"+RealmConf._dbHost+"/"+RealmConf._dbName,RealmConf._dbUser,RealmConf._dbPass);
						Statement stat = DB.createStatement();
						ResultSet RS = stat.executeQuery(
								"SELECT subscribe_end,gmlevel,pseudo,question,answer FROM accounts "+
								"WHERE guid = '"+guid+"';");
						//If there is no account
						if(!RS.first())
						{
							RS.close();
							stat.close();
							DB.close();
							return null;
						}
						Account acc = new Account(guid, 
								RS.getLong("subscribe_end"),
								RS.getByte("gmlevel"),
								RS.getString("pseudo"),
								RS.getString("question"),
								RS.getString("answer"));
						
						//Close all resources
						RS.close();
						stat.close();
						DB.close();
						
						return acc;
					}
				});
		//Try to get the account, else return null
		try {
			return account.get();
		} catch (Exception e) { e.printStackTrace(); return null; }
	}

	public static byte getCharNumbByAccount(final byte server, final long guid)
	{
		if(_self==null)return -1;
		
		Future<Byte> numb = Exec.submit(new Callable<Byte>()
				{
					public Byte call() throws Exception
					{
						Connection DB = DriverManager.getConnection("jdbc:mysql://"+RealmConf._dbHost+"/"+RealmConf._dbName,RealmConf._dbUser,RealmConf._dbPass);
						Statement stat = DB.createStatement();
						ResultSet RS = stat.executeQuery(
								"SELECT number FROM servers_characters "+
								"WHERE server = '"+server+"' AND guid = '"+guid+"';");
						
						//If there is no account
						if(!RS.first())
						{
							RS.close();
							stat.close();
							DB.close();
							return 0;
						}
						
						byte numb = RS.getByte(1);
						
						//Close all resources
						RS.close();
						stat.close();
						DB.close();
                                                
						CharacterNumb = numb;
                                                
						return numb;
					}
				});
				
		try {
			return numb.get();
		} catch (Exception e) { e.printStackTrace(); return 0; }
	}
}
